﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Partial Class Report_RP01_2
    Inherits System.Web.UI.Page

    Dim Report As Object

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012"

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2

        If Session("users") Is Nothing Then
            Response.Redirect("../LoginForm.aspx")
        End If


        Getdata()


    End Sub


     

    Function Getdata() As Decimal


        Dim adapter As New Data_ReportTableAdapters.R01TableAdapter
        Dim sql As String
        Dim dt As New Data_Report.R01DataTable


        'sql = " SELECT "

        'sql += " CASE ISNULL(BudgetDetails.Project_ProjectCode,'') WHEN '' THEN N'งบประมาณประจำ' ELSE N'งบประมาณโครงการ' END AS BudgetType,"
        'sql += " h1.Description,"
        'sql += " h2.Description,"
        'sql += " (Cmmts.CmmtCode + '  ' + Cmmts.Description) as Description,"
        'sql += " SUM(BudgetDetails.Amount)"
        'sql += " from BudgetDetails"
        'sql += " inner join FundCenters on "
        'sql += "  FundCenters.FundCenterCode = BudgetDetails.FundCenter_FundCenterCode And FundCenters.PlanYear = BudgetDetails.FundCenter_PlanYear"
        'sql += " inner join Cmmts on "
        'sql += "  Cmmts.CmmtCode = BudgetDetails.Cmmt_CmmtCode And Cmmts.PlanYear = BudgetDetails.Cmmt_PlanYear"
        'sql += " inner join CmmtHierarchies h3 on"
        'sql += " h3.Cmmt_CmmtCode = BudgetDetails.Cmmt_CmmtCode And"
        'sql += " h3.Cmmt_PlanYear = BudgetDetails.Cmmt_PlanYear"
        'sql += " inner join CmmtHierarchies h2 on"
        'sql += " h3.ParentCmmtHierarchy_Id = h2.Id"
        'sql += " inner join CmmtHierarchies h1 on"
        'sql += " h2.ParentCmmtHierarchy_Id = h1.Id"


        'sql += " where  BudgetDetails.PlanYear  = " & Session("year") & " and Version = " & Session("version")


        'If Val(Session("CmmtCodeS")) <> 0 Then
        '    sql += " and  Cmmts.CmmtCode  between " & Session("CmmtCodeS") & " and " & Session("CmmtCodeE")
        'End If




        'sql += " Group by   BudgetDetails.FundCenter_PlanYear,  "
        'sql += " BudgetDetails.Project_ProjectCode, "
        'sql += " h1.Id, h1.Description, "
        'sql += " h2.Id, h2.Description, "
        'sql += " BudgetDetails.Cmmt_PlanYear, BudgetDetails.Cmmt_CmmtCode, Cmmts.Description, Cmmts.CmmtCode "



        sql = " select"



        sql += "  CASE ISNULL(BudgetDetails.Project_ProjectCode,'')  WHEN '' THEN N'งบประมาณประจำ' ELSE N'งบประมาณโครงการ' END AS BudgetType,"

        sql += "  ISNULL(BudgetDetails.Project_ProjectCode,'') as ProjectCode ,"

        sql += "  ISNULL(p.Description,'') as ProjectDescription,"

        sql += "  h1.Description,   h2.Description, (Cmmts.CmmtCode + '  ' + Cmmts.Description) as Description,"

        sql += "  SUM(BudgetDetails.Amount)"

        sql += "  from BudgetDetails"

        sql += " inner join FundCenters on"

        sql += " FundCenters.FundCenterCode = BudgetDetails.FundCenter_FundCenterCode And FundCenters.PlanYear = BudgetDetails.FundCenter_PlanYear"

        sql += " inner join Cmmts on"

        sql += "  Cmmts.CmmtCode = BudgetDetails.Cmmt_CmmtCode And Cmmts.PlanYear = BudgetDetails.Cmmt_PlanYear"

        sql += " inner join CmmtHierarchies h3 on"

        sql += " h3.Cmmt_CmmtCode = BudgetDetails.Cmmt_CmmtCode and"

        sql += "  h3.Cmmt_PlanYear = BudgetDetails.Cmmt_PlanYear"

        sql += " inner join CmmtHierarchies h2 on"

        sql += "  h3.ParentCmmtHierarchy_Id = h2.Id"

        sql += " inner join CmmtHierarchies h1 on"

        sql += "  h2.ParentCmmtHierarchy_Id = h1.Id"

        sql += " left join Projects p on"

        sql += "  (p.ProjectCode = BudgetDetails.Project_ProjectCode and"

        sql += "  p.PlanYear = BudgetDetails.Project_PlanYear ) or p.ProjectCode IS NULL"


        sql += " where  BudgetDetails.PlanYear  = " & Session("year") & " and Version = " & Session("version")

        If Session("FCTRmode") = "1" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If

        If Val(Session("CmmtCodeS")) <> 0 Then
            sql += " and  Cmmts.CmmtCode  between " & Session("CmmtCodeS") & " and " & Session("CmmtCodeE")
        End If


        sql += " Group by"

        sql += " BudgetDetails.FundCenter_PlanYear,"

        sql += " BudgetDetails.Project_ProjectCode,p.Description,"

        sql += "  h1.Id,h1.Description,"

        sql += "  h2.Id,h2.Description,"

        sql += " BudgetDetails.Cmmt_PlanYear, BudgetDetails.Cmmt_CmmtCode, Cmmts.Description,Cmmts.CmmtCode"




        adapter.SearchNews(dt, sql)


        Dim das As New DataSet
        das.DataSetName = "R01DS"
        das.Tables.Add(dt)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
        'ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "Report\R01.rdlc"
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables(0)))
        ReportViewer1.DocumentMapCollapsed = True

        Dim UserName As String = Session("users")
        Dim years As String = Session("year")
        Dim version As String = Session("version")
        Dim users As String = Session("users")

        Dim p1, p2, p3 As ReportParameter
        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("version", version)
        p3 = New ReportParameter("username", UserName)

        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.SetParameters(p3)


        ReportViewer1.LocalReport.Refresh()





        'Response.Write(dt.Rows.Count & sql)
        Session("count") = dt.Rows.Count



        Return dt.Rows.Count

    End Function
    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

End Class
